﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.OleDb;

namespace DAO
{
    public class KhachHangDAO
    {
        public List<KhachHangDTO> LayThongTinKH()
        {
            string sql = "SELECT MaKH,TenKH,LoaiKH,GTinh,DChi,DThoai,TongCV ";
            sql = sql + " FROM KHACHHANG ";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<KhachHangDTO> DanhSach = new List<KhachHangDTO>();
            while (reader.Read())
            {
                KhachHangDTO khDTO = new KhachHangDTO();
                khDTO.MaKH = reader.GetString(0);
                khDTO.TenKH = reader.GetString(1);
                khDTO.LoaiKH = reader.GetInt32(2);
                khDTO.GioiTinh = reader.GetString(3);
                khDTO.DiaChi = reader.GetString(4);
                khDTO.DThoai = reader.GetString(5);
                khDTO.TongCV = reader.GetInt32(6);
                DanhSach.Add(khDTO);
            }
            conn.Close();
            return DanhSach;
        }
        public List<KhachHangDTO> LayKHtheoMaKH(string maKH)
        {
            string sql = "SELECT MaKH,TenKH,DChi,GTinh,DThoai,TongCV,LoaiKH ";
            sql = sql + " FROM KHACHHANG WHERE MaKH = '" + maKH + "'";
 
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<KhachHangDTO> DanhSach = new List<KhachHangDTO>();
            while (reader.Read())
            {
                KhachHangDTO khDTO = new KhachHangDTO();
                //khDTO.MaKH = reader.GetString(0);
                khDTO.TenKH = reader.GetString(1);
                khDTO.DiaChi = reader.GetString(2);
                khDTO.GioiTinh = reader.GetString(3);
                khDTO.DThoai = reader.GetString(4);
                khDTO.TongCV = reader.GetInt32(5);
                khDTO.LoaiKH = reader.GetInt32(6);
                DanhSach.Add(khDTO);
            }
            conn.Close();
            return DanhSach;

        }
        public string LayMaKHLonNhat() // Lay MaKH lon nhat
        {
            string sql = "SELECT TOP  1, MaKH FROM KHACHHANG  ORDER BY MaKH DESC  ";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            KhachHangDTO khDTO = new KhachHangDTO();
            while (reader.Read())
            {

                khDTO.MaKHLN = reader.GetString(0);
            }
            conn.Close();
            return khDTO.MaKHLN;
        }
        public string NextID(string lastID, string prefixID)
        {
            if (lastID == "" || lastID == " " || lastID == null)
            {
                return prefixID + "001";  // fixwidth default
            }
            int nextID = int.Parse(lastID.Remove(0, prefixID.Length)) + 1;
            int lengthNumerID = lastID.Length - prefixID.Length;
            string zeroNumber = "";
            for (int i = 1; i <= lengthNumerID; i++)
            {
                if (nextID < Math.Pow(10, i))
                {
                    for (int j = 1; j <= lengthNumerID - i; i++)
                    {
                        zeroNumber += "0";
                    }
                    return prefixID + zeroNumber + nextID.ToString();
                }
            }
            return prefixID + nextID;

        }
        public string TaoMaKHMoi()
        {
            string LastID = LayMaKHLonNhat();
            string NewID = NextID(LastID, "KH");
            return NewID;
        }
        public int ThemKhachHang(KhachHangDTO khDTO)
        {
            //tao ket noi csdl
            OleDbConnection conn = DataProvider.ConnectDB();
            //viet sql
            string sql = "INSERT INTO KHACHHANG(MaKH,TenKH,LoaiKH,GTinh,DChi,DThoai,TongCV) ";
            sql = sql + " VALUES(@MaKH,@TenKH,@LoaiKH,@GTinh,@DChi,@DThoai,0) ";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para = cmd.Parameters.Add("@MaKH", OleDbType.VarChar);
            para.Value = khDTO.MaKH;
            para = cmd.Parameters.Add("@TenKH", OleDbType.VarChar);
            para.Value = khDTO.TenKH;
            para = cmd.Parameters.Add("@LoaiKH", OleDbType.VarChar);
            para.Value = khDTO.LoaiKH;
            para = cmd.Parameters.Add("@GTinh", OleDbType.VarChar);
            para.Value = khDTO.GioiTinh;
            para = cmd.Parameters.Add("@DChi", OleDbType.VarChar);
            para.Value = khDTO.DiaChi;
            para = cmd.Parameters.Add("@DThoai", OleDbType.VarChar);
            para.Value = khDTO.DThoai;

            int kq = cmd.ExecuteNonQuery();

            conn.Close();
            if (kq >= 0)
                return 1;
            return 0;

        }
        public List<KhachHangDTO> LayThongTinKhachHangTheoLoai(int LoaiKH)
        {
            string sql = "SELECT MaKH,TenKH,GTinh,DChi,DThoai,TongCV ";
            sql = sql + " FROM KHACHHANG WHERE LoaiKH="+LoaiKH;
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<KhachHangDTO> DanhSach = new List<KhachHangDTO>();
            while (reader.Read())
            {
                KhachHangDTO khDTO = new KhachHangDTO();
                khDTO.MaKH = reader.GetString(0);
                khDTO.TenKH = reader.GetString(1);
                khDTO.GioiTinh = reader.GetString(2);
                khDTO.DiaChi = reader.GetString(3);
                khDTO.DThoai = reader.GetString(4);
                khDTO.TongCV = reader.GetInt32(5);
                DanhSach.Add(khDTO);
            }
            conn.Close();
            return DanhSach;
        }
        public int LayLoaiKhachHang(string MaKH)
        {
            string sql = "SELECT LoaiKH FROM KHACHHANG WHERE MaKH = '"+ MaKH +"'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            KhachHangDTO khDTO = new KhachHangDTO();
            while (reader.Read())
            {
                khDTO.LoaiKH = reader.GetInt32(0);
            }
            conn.Close();
            return khDTO.LoaiKH;
        }
        public string LayDiaChiKhachHang(string MaKH)
        {
            string sql = "SELECT DChi FROM KHACHHANG WHERE MaKH = '" + MaKH + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            KhachHangDTO khDTO = new KhachHangDTO();
            while (reader.Read())
            {
                khDTO.DiaChi = reader.GetString(0);
            }
            conn.Close();
            return khDTO.DiaChi;
        }
        public string LayDienThoaiKhachHang(string MaKH)
        {
            string sql = "SELECT DThoai FROM KHACHHANG WHERE MaKH = '" + MaKH + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            KhachHangDTO khDTO = new KhachHangDTO();
            while (reader.Read())
            {
                khDTO.DThoai = reader.GetString(0);
            }
            conn.Close();
            return khDTO.DThoai;
        }
        public int CapNhatDiemCV(int DiemCV,string MaKH)
        {
            string sql = "UPDATE KHACHHANG SET TongCV=" + DiemCV + " WHERE MaKH= '" + MaKH + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            int res = cmd.ExecuteNonQuery();
            conn.Close();
            return res;
        }
        public int LayDiemCV(string MaKH)
        {
            string sql = "SELECT TongCV FROM KHACHHANG WHERE MaKH='" + MaKH + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            reader.Read();
            Int32 diemcv=reader.GetInt32(0);
            conn.Close();
            return diemcv;
        }
        public string LayTenKhachHang(string MaKH)
        {
            string sql = "SELECT TenKH FROM KHACHHANG WHERE MaKH = '" + MaKH + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            
            KhachHangDTO khDTO = new KhachHangDTO();
            while (reader.Read())
            {
                khDTO.TenKH = reader.GetString(0);
            }
            conn.Close();
            return khDTO.TenKH;
        }
        public int XoaKH(string MaKH)
        {
            string sql = "DELETE FROM KHACHHANG WHERE MaKH = '"+MaKH + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            int res= cmd.ExecuteNonQuery();
            conn.Close();
            return res;
        }
        public List<KhachHangDTO> TiemKiemKhachHang(string timkiem)
        {
            string sql = "SELECT MaKH,TenKH,DChi,GTinh,DThoai,TongCV ";
            sql = sql + " FROM KHACHHANG WHERE MaKH LIKE '%" + timkiem + "%' OR TenKH LIKE '%" + timkiem + "%'" ;

            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<KhachHangDTO> DanhSach = new List<KhachHangDTO>();
            while (reader.Read())
            {
                KhachHangDTO khDTO = new KhachHangDTO();
                khDTO.MaKH = reader.GetString(0);
                khDTO.TenKH = reader.GetString(1);
                khDTO.DiaChi = reader.GetString(2);
                khDTO.GioiTinh = reader.GetString(3);
                khDTO.DThoai = reader.GetString(4);
                khDTO.TongCV = reader.GetInt32(5);
                DanhSach.Add(khDTO);
            }
            conn.Close();
            return DanhSach;

        }
        public int CapNhatThongTinKhachHang(KhachHangDTO khDTO)
        {
            string sql = "UPDATE KHACHHANG";
            sql=sql+" SET MaKH='"+ khDTO.MaKH +"',TenKH='"+ khDTO.TenKH +"',LoaiKH="+ khDTO.LoaiKH +",GTinh='"+ khDTO.GioiTinh+"',DChi='"+ khDTO.DiaChi +"',DThoai='"+khDTO.DThoai+"'";
            sql=sql+" WHERE MaKH= '" + khDTO.MaKH + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            int res = cmd.ExecuteNonQuery();
            conn.Close();
            return res;
        }
        
    }
}
